package com.aios.dao;

import com.aios.entity.IrisUser;
import com.aios.entity.IrisUserExample;
import com.aios.entity.mapper.AssignedState;
import com.aios.entity.mapper.IrisUserHasUserGroups;

import java.util.List;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Select;

public interface IrisUserMapper {

	int countByExample(IrisUserExample example);
	int deleteByExample(IrisUserExample example);
	int deleteByPrimaryKey(Integer id);
	int insert(IrisUser record);
	int insertSelective(IrisUser record);
	int insertWithoutID(IrisUser record);
	List<IrisUser> selectByExample(IrisUserExample example);
	IrisUser selectByPrimaryKey(Integer id);

	int updateByExampleSelective(@Param("record") IrisUser record,
			@Param("example") IrisUserExample example);

	int updateByExample(@Param("record") IrisUser record,
			@Param("example") IrisUserExample example);

	int updateByPrimaryKeySelective(IrisUser record);

	int updateByPrimaryKey(IrisUser record);

	@Select("SELECT * FROM iris_user WHERE STATUS = #{status} ORDER BY SORT_ORDER")
	@ResultMap(value = "MiniResultMap")
	List<IrisUser> findAllByStatus(@Param("status") Integer status);
	
	@Select("SELECT * FROM iris_user WHERE STATUS = #{status} ORDER BY SORT_ORDER")
	@ResultMap(value = "MiniResultMap")
	List<IrisUser> findAllByStatusAndLimit(@Param("status") Integer status, Integer start, Integer size);
	
	@Select("SELECT COUNT(*) FROM iris_user WHERE STATUS = #{status}")
	Integer countAllByStatus(@Param("status") Integer status);
	
	@Select("SELECT COUNT(*) FROM iris_user WHERE STATUS = #{status} AND NAME LIKE CONCAT('%',#{search_term},'%') OR EMPLOYEE_ID LIKE CONCAT('%',#{search_term},'%')")
	Integer countAllByStatusSearched(@Param("status") Integer status, @Param("search_term") String searchTerm);

	@Select("SELECT * FROM iris_user WHERE STATUS = #{status} ORDER BY SORT_ORDER")
	@ResultMap(value = "UserGroupJoinResultMap")
	List<IrisUserHasUserGroups> findAllWithGroups(@Param("status") Integer status);
	
	@Select("SELECT * FROM iris_user WHERE STATUS = #{status} ORDER BY SORT_ORDER LIMIT #{start}, #{size}")
	@ResultMap(value = "UserGroupJoinResultMap")
	List<IrisUserHasUserGroups> findListPaginated(@Param("status") Integer status, 
			@Param("start") Integer start, @Param("size") Integer size);
	
	@Select("SELECT * FROM iris_user WHERE STATUS = #{status} AND NAME LIKE CONCAT('%',#{search_term},'%') OR EMPLOYEE_ID LIKE CONCAT('%',#{search_term},'%') ORDER BY SORT_ORDER LIMIT #{start}, #{size}")
	@ResultMap(value = "UserGroupJoinResultMap")
	List<IrisUserHasUserGroups> findSearchListPaginated(@Param("status") Integer status, 
			@Param("start") Integer start, @Param("size") Integer size, @Param("search_term") String searchTerm);

	@Select("SELECT * FROM iris_user WHERE EMAIL = #{email}")
	@ResultMap(value = "MiniResultMap")
	List<IrisUser> findByEmailAddress(@Param("email") String email);

	@Select("SELECT g.ID as ID, g.GROUP_NAME as NAME, true as STATE FROM iris_user_has_user_group ig,user_group g WHERE ig.USER_GROUP_ID=g.ID AND ig.IRIS_USER_ID=#{id} ORDER BY g.SORT_ORDER")
	@ResultMap(value = "UserGroupResultMap")
	List<AssignedState> findAssignedGroups(@Param("id") Integer id);

	@Select("SELECT ug.ID,ug.GROUP_NAME AS NAME, false as STATE FROM user_group ug " +
			"WHERE ug.ID NOT IN " +
			"(SELECT iuhug.USER_GROUP_ID FROM iris_user_has_user_group iuhug " +
			"WHERE iuhug.IRIS_USER_ID = #{id} GROUP BY iuhug.USER_GROUP_ID)")
	@ResultMap(value = "UserGroupResultMap")
	List<AssignedState> findAvailableGroups(@Param("id") Integer id);
	
	@Select("SELECT * FROM iris_user WHERE ID = #{id}")
	@ResultMap(value = "UserGroupJoinResultMap")
	IrisUserHasUserGroups findByIdWithGroups(@Param("id") Integer id);
	
	@Select("SELECT USER_GROUP_ID as ID FROM iris_user_has_user_group WHERE IRIS_USER_ID = #{id} ORDER BY SORT_ORDER")
	@ResultMap(value = "ExistingGroupsResultMap")
	List<Integer> findExistingGroupList(@Param("id") Integer id);
	
	@Select("SELECT * FROM iris_user WHERE EMAIL = #{email} AND ID != #{id}")
	@ResultMap(value = "MiniResultMap")
	List<IrisUser> findEmailNotWithId(@Param("email") String email, @Param("id") Integer id);
		
	@Select("SELECT * FROM iris_user WHERE STATUS = #{status} AND NAME LIKE CONCAT('%',#{search_term},'%') OR EMPLOYEE_ID LIKE CONCAT('%',#{search_term},'%') ORDER BY ${sortColumn} ${sortOrder} LIMIT #{start}, #{size}")
	@ResultMap(value = "UserGroupJoinResultMap")
	List<IrisUserHasUserGroups> findSortedListPaginated(@Param("status") int statusValue, @Param("start") Integer start,
			@Param("size") Integer size, @Param("sortColumn") String sortColumn, @Param("sortOrder") String sortOrder, @Param("search_term") String searchTerm);
		
}